package mediateur;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import connexion.ConnexionAccess;
import connexion.ConnexionExcel;
import connexion.ConnexionOracle;

public class Mediateur {
	ConnexionOracle co;
	ConnexionExcel ce;
	ConnexionAccess ca;
	
	Connection cto;
	Connection cte;
	Connection cta;
	
	Statement sto;
	Statement ste;
	Statement sta;
	
	public Mediateur() {
		co = new ConnexionOracle();
		ce = new ConnexionExcel();
		ca = new ConnexionAccess();
	}
	
	public void connectAll() {
		cto = co.connect();
		cte = ce.connect();
		cta = ca.connect();
		
		try {
			sto = cto.createStatement();
			ste = cte.createStatement();
			sta = cta.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void disconnectAll() {
		co.disconnect();
		ce.disconnect();
		ca.disconnect();
	}
	
	public ArrayList<String> getClientsMrFromAgences(){
		ArrayList<String> resultArray = new ArrayList<>(); 
		String reqOracle = "SELECT operation.Libelle " +
							"FROM client, compte, operation " +
							"WHERE client.NumCl=compte.NumCl " +
							"AND compte.NumCp=operation.NumCp " +
							"AND client.état_civil='mr'";
		String reqExcel = "SELECT [Operation$].Libelle FROM [compte$],[Client$],[Operation$] WHERE [Client$].NumCl=[compte$].NumCl AND [compte$].NumCp=[Operation$].NumCp AND [Client$].Civilite='MR'";
		String reqAccess = "SELECT Operation.Libelle " +
							"FROM Client, compte, Operation " +
							"WHERE Client.NumCl=compte.NumCl " +
							"AND compte.NumCp=Operation.NumCp " +
							"AND Client.civilite='MR'";
		
		try {
			ResultSet rso = sto.executeQuery(reqOracle);
			ResultSet rse = ste.executeQuery(reqExcel);
			ResultSet rsa = sta.executeQuery(reqAccess);
			
			while(rso.next())
				resultArray.add("Agence 1"+rso.getString(1));
			while(rse.next())
				resultArray.add("Agence 3"+rse.getString(1));
			while(rsa.next())
				resultArray.add("Agence 2"+rsa.getString(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return resultArray;
	}
	
	public ArrayList<Integer> getOlderRichestClient() {
		ArrayList<Integer> al = new ArrayList<Integer>();
  
		String reqOracle = "SELECT c.DateNaiss from Client c, Compte cp Where cp.NumCl = c.numCl and cp.solde = (Select max(solde) from compte)";

		String reqExcel = "select [Client$].age FROM [compte$],[Client$] Where [compte$].NumCl = [Client$].NumCl and [compte$].solde= (select max(solde) from [compte$])";
		String reqAccess = "SELECT c.Date_naissance from Client c, Compte cp Where cp.NumCl = c.numCl and cp.solde=(Select max(Compte.Solde) from compte)";
		try {
			
			ResultSet rso = sto.executeQuery(reqOracle);
			ResultSet rse = ste.executeQuery(reqExcel);
			ResultSet rsa = sta.executeQuery(reqAccess);

			rso.next();
			int ageOracle = getYears(rso.getDate(1));
			rse.next();
			int ageExcel = rse.getInt(1);
			rsa.next();
			int ageAccess = getYears(rsa.getDate(1));

			al.add(ageOracle);
			al.add(ageExcel);
			al.add(ageAccess);
   
			return al;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return al;
	}
	
	public static int getYears(Date d)
	{
	  Calendar curr = Calendar.getInstance();
	  Calendar birth = Calendar.getInstance();
	  birth.setTime(d);
	  int yeardiff = curr.get(Calendar.YEAR) - birth.get(Calendar.YEAR);
	  curr.add(Calendar.YEAR,-yeardiff);
	  if(birth.after(curr))
	  {
	    yeardiff = yeardiff - 1;
	  }
	  return yeardiff;
	}
	
	public static void main(String[] args) {
		Mediateur m = new Mediateur();
		ArrayList<String> outputQ1 = new ArrayList<>();
		ArrayList<Integer> outputQ2 = new ArrayList<>();
		
		m.connectAll();
		
		outputQ1 = m.getClientsMrFromAgences();
		System.out.println("Opérations effectuées par les clients masculins des trois agences");
		for(String s : outputQ1)
			System.out.println(s);
		
		outputQ2 = m.getOlderRichestClient();
		System.out.println("Age du client ayant le solde le plus élevé");
		for(Integer s : outputQ2)
			System.out.println(s);
	}
}
